﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using AliCMS;
using System.Web;

namespace AliCMS.CourseModule.OleDb
{
	public class Collect : OleDbBase, Dal.ICollect
	{
		public int GetInnerIDFromGuid(Guid guid)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.Collect.ID, MapType(Alias.Collect.ID)).Value = guid;
			Command.CommandText = "SELECT " + MapColumn(Alias.Collect.InnerID) + " FROM " + MapTable(Alias.Objects.Collect) + " WHERE " + MapColumn(Alias.Collect.ID) + "=?";
			object result = Command.ExecuteScalar();
			if (result == null)
				return 0;
			else
				return (int)result;
		}
		public Guid GetGuidFromInnerID(object id)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.Collect.InnerID, MapType(Alias.Collect.InnerID)).Value = id;
			Command.CommandText = "SELECT " + MapColumn(Alias.Collect.ID) + " FROM " + MapTable(Alias.Objects.Collect) + " WHERE " + MapColumn(Alias.Collect.InnerID) + "=?";
			object result = Command.ExecuteScalar();
			if (result == null)
				return Guid.Empty;
			else
				return (Guid)result;
		}
		public Dictionary<string, object> Get(object id)
		{
			Dictionary<string, object> result = new Dictionary<string, object>();

			StringBuilder sql = new StringBuilder("SELECT TOP 1");
			sql.Append(" " + MapColumn(Alias.Collect.ID));
			sql.Append("," + MapColumn(Alias.Collect.InnerID));
			sql.Append("," + MapColumn(Alias.Collect.UserID));
			sql.Append("," + MapColumn(Alias.Collect.CourseID));
			sql.Append(" FROM " + MapTable(Alias.Objects.Collect));

			if (id is Guid)
			{
				sql.Append(" WHERE " + MapColumn(Alias.Collect.ID) + "=?");

				Command.Parameters.Clear();
				Command.Parameters.Add("@" + Alias.Collect.ID, MapType(Alias.Collect.ID)).Value = id;
			}
			else
			{
				sql.Append(" WHERE " + MapColumn(Alias.Collect.InnerID) + "=?");

				Command.Parameters.Clear();
				Command.Parameters.Add("@" + Alias.Collect.InnerID, MapType(Alias.Collect.InnerID)).Value = id;
			}

			Command.CommandText = sql.ToString();
			DataReader = Command.ExecuteReader();
			if (!DataReader.HasRows)
			{
				DataReader.Close();
				return null;
			}

			DataReader.Read();
			int i = 0;
			result[Alias.Collect.ID] = DataReader[i++];
			result[Alias.Collect.InnerID] = DataReader[i++];
			result[Alias.Collect.UserID] = DataReader[i++];
			result[Alias.Collect.CourseID] = DataReader[i++];
			DataReader.Close();

			return result;
		}
		public void Add(Dictionary<string, object> item)
		{
			Command.Parameters.Clear();

			#region 设置RowLang
			item.Add(Alias.Collect.RowLang, "|" + Lang + "|");
			#endregion

			StringBuilder names = new StringBuilder();
			StringBuilder values = new StringBuilder();

			Command.Parameters.Clear();
			PrepareParameters(names, values, item);

			Command.CommandText = "INSERT INTO " + MapTable(Alias.Objects.Collect) + "(" + names + ") VALUES(" + values + ")";
			Command.ExecuteNonQuery();
		}
		public void Modify(int id, Dictionary<string, object> changes)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.Collect.InnerID, MapType(Alias.Collect.InnerID)).Value = id;

			#region 检查并设置RowLang
			Command.CommandText = "SELECT " + MapColumn(Alias.Collect.RowLang) + " FROM " + MapTable(Alias.Objects.Collect) + " WHERE " + MapColumn(Alias.Collect.InnerID) + "=?";
			string rowLang = Command.ExecuteScalar().ToString();
			if (!rowLang.Contains("|" + Lang + "|"))
				changes.Add(Alias.Collect.RowLang, rowLang + Lang + "|");
			#endregion

			StringBuilder sql = new StringBuilder("UPDATE " + MapTable(Alias.Objects.Collect) + " SET ");
			Command.Parameters.Clear();
			PrepareParameters(sql, changes);
			Command.Parameters.Add("@" + Alias.Collect.InnerID, MapType(Alias.Collect.InnerID)).Value = id;

			sql.Append(" WHERE " + MapColumn(Alias.Collect.InnerID) + "=?");

			Command.CommandText = sql.ToString();
			Command.ExecuteNonQuery();
		}
		public void Delete(int id)
		{
			Command.Parameters.Clear();
			Command.Parameters.Add("@" + Alias.Collect.InnerID, MapType(Alias.Collect.InnerID)).Value = id;

			StringBuilder sql = new StringBuilder("DELETE FROM " + MapTable(Alias.Objects.Collect) + " WHERE " + MapColumn(Alias.Collect.InnerID) + "=?");

			Command.CommandText = sql.ToString();
			Command.ExecuteNonQuery();
		}
		public DataTable List(int page, int pageSize, string condition, string sort)
		{
			DataTable result = new DataTable();
			result.Columns.Add(Alias.Collect.ID, MapCSType(Alias.Collect.ID));
			result.Columns.Add(Alias.Collect.InnerID, MapCSType(Alias.Collect.InnerID));
			result.Columns.Add(Alias.Collect.UserID, MapCSType(Alias.Collect.UserID));
			result.Columns.Add(Alias.Collect.CourseID, MapCSType(Alias.Collect.CourseID));
			result.Columns.Add(Alias.Course.InnerID, MapCSType(Alias.Course.InnerID));
			result.Columns.Add(Alias.Course.Name, MapCSType(Alias.Course.Name));
			result.Columns.Add(Alias.Course.Thumbnail, MapCSType(Alias.Course.Thumbnail));

			#region 条件
			Command.Parameters.Clear();
			string join = " INNER JOIN " + MapTable(Alias.Objects.Course) + " AS i ON i." + MapColumn(Alias.Course.ID) + "=c." + MapColumn(Alias.Collect.CourseID);
			StringBuilder where = new StringBuilder();
			if (!String.IsNullOrEmpty(condition))
			{
				where.Append(" WHERE " + condition);
				ParseCondition(where, "c");
			}
			#endregion

			#region 排序
			if (!String.IsNullOrEmpty(sort))
				sort = " ORDER BY " + ParseSort(sort, "c");
			else
				sort = " ORDER BY c." + MapColumn(Alias.Collect.InnerID) + " DESC";
			#endregion

			#region 构造SQL语句
			StringBuilder sql = new StringBuilder("SELECT");
			if (page > 0 && pageSize > 0)
				sql.Append(" TOP " + (page * pageSize));
			sql.Append(" c." + MapColumn(Alias.Collect.ID));
			sql.Append(",c." + MapColumn(Alias.Collect.InnerID));
			sql.Append(",c." + MapColumn(Alias.Collect.UserID));
			sql.Append(",c." + MapColumn(Alias.Collect.CourseID));
			sql.Append(",i." + MapColumn(Alias.Course.InnerID));
			sql.Append(",i." + MapColumn(Alias.Course.Name, Lang));
			sql.Append(",i." + MapColumn(Alias.Course.Thumbnail));
			sql.Append(" FROM " + MapTable(Alias.Objects.Collect) + " AS c");
			sql.Append(join + where + sort);
			Command.CommandText = sql.ToString();
			#endregion

			#region 执行SQL语句并获取记录
			int curIndex = 0, rowIndex = 0;
			int startIndex = (page - 1) * pageSize;
			DataReader = Command.ExecuteReader();
			while (DataReader.Read())
			{
				if (pageSize != -1)
				{
					if (curIndex < startIndex)
					{
						curIndex++;
						continue;
					}

					if (rowIndex == pageSize)
						break;
				}

				DataRow row = result.NewRow();
				int i = 0;
				row[Alias.Collect.ID] = DataReader[i++];
				row[Alias.Collect.InnerID] = DataReader[i++];
				row[Alias.Collect.UserID] = DataReader[i++];
				row[Alias.Collect.CourseID] = DataReader[i++];
				row[Alias.Course.InnerID] = DataReader[i++];
				row[Alias.Course.Name] = DataReader[i++];
				row[Alias.Course.Thumbnail] = DataReader[i++];
				result.Rows.Add(row);

				rowIndex++;
			}
			DataReader.Close();
			#endregion

			return result;
		}
		public int GetRecordCount(string condition)
		{
			#region 条件
			Command.Parameters.Clear();
			StringBuilder where = new StringBuilder();
			if (!String.IsNullOrEmpty(condition))
			{
				where.Append(" WHERE " + Alias.Collect.RowLang + " LIKE '%|" + Lang + "|%' AND ");
				where.Append("(" + condition + ")");

				ParseCondition(where, "");
			}
			else
			{
				where.Append(" WHERE " + MapColumn(Alias.Collect.RowLang) + " LIKE '%|" + Lang + "|%'");
			}
			#endregion

			Command.CommandText = "SELECT COUNT(*) FROM " + MapTable(Alias.Objects.Collect) + where;
			return Convert.ToInt32(Command.ExecuteScalar());
		}
	}
}
